package com.sample.springbatch.job.demo.jobtransactional;


import com.sample.springbatch.model.User;
import com.sample.springbatch.model.UserLog;
import com.sample.springbatch.repository.UserLogRepository;
import com.sample.springbatch.thread.ThreadPoolService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * @author zwg
 * @date 2024/7/15 10:12
 */
@Slf4j
@Service
public class UserService {

    @Autowired
    ThreadPoolService poolService;

    @Autowired
    NamedParameterJdbcTemplate jdbcTemplate;
    //@Autowired
    //UserLogRepository userLogRepository;


    //@Transactional(propagation = Propagation.REQUIRES_NEW)
    public UserLog updateUserAge(User user){
        //验证主线程 更新非异步线程数据， 结论：不影响异步线程处理
        /*String sql = "update User u set u.last_name=u.first_name where u.id=:id";
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("id",user.getId());

        log.info("user id:{}, age:{}",user.getId(),user.getAge());

        jdbcTemplate.update(sql, paramMap);*/


        UserLog userLog = new UserLog(user.getId(), user.getLastName(), user.getFirstName(),user.getAge());

        //主线程新增数据，异步线程使用更新    结论：影响异步线程提交
        /*String sql = "insert into user_log(user_id,age) values (:userId,:age)";
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("age", user.getAge());
        paramMap.put("userId",user.getId());*/
        //无法自动获取自增主键
        //jdbcTemplate.update(sql, paramMap);

        KeyHolder keyHolder = new GeneratedKeyHolder();
        //获取自增主键
        /*jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql,new MapSqlParameterSource(paramMap)
                        , Statement.RETURN_GENERATED_KEYS);
                return ps;
            }
        }, keyHolder);*/
        //jdbcTemplate.update(sql,new MapSqlParameterSource(paramMap),keyHolder);
        //int id = keyHolder.getKey().intValue();
        //userLog.setId(id);
        userLog.setId(170+ userLog.getUserId());

        //验证主线程 更新异步线程数据， 结论：影响异步线程处理
        /*String sql = "update User_log u set age=age+1, update_time=now() where u.id=:id";
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("id",userLog.getId());
        int res = jdbcTemplate.update(sql, paramMap);*/

        log.info("insert user_log: id:{}",userLog.getId());

        poolService.runTask(new UpdateUserTask(userLog));
        return userLog;
    }



    class UpdateUserTask implements Runnable{

        UserLog userLog;

        UpdateUserTask(UserLog log){
            this.userLog = log;
        }

        @Override
        public void run() {
            try{

                //Thread.currentThread().sleep(1500L);
                //Optional<UserLog> userLogOpt = userLogRepository.findById(userLog.getId());
                //log.info("get user_log id:{}",userLogOpt.orElse(new UserLog()).getId());


                Map<String,Object> paramMap = new HashMap<>();
                paramMap.put("id",userLog.getId());

                String select_sql = "select id from user_log where id=:id";
                List<Map<String,Object>> dataList = jdbcTemplate.query(select_sql,paramMap,new RowMapper<Map<String,Object>>(){
                    @Override
                    public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                        if(rs != null){
                            Map<String,Object> resultMap = new HashMap<>();
                            resultMap.put("id",rs.getString("id"));
                            return resultMap;
                        }
                        return null;
                    }
                });
                log.info("query user_log:{}",dataList);

                String sql = "update User_log u set age=age+1, update_time=now() where u.id=:id";
                int res = jdbcTemplate.update(sql, paramMap);
                log.info("user_log result:{} id:{} userId:{} age:{}",res,userLog.getId(),userLog.getUserId(),userLog.getAge());

                //Thread.currentThread().sleep(1500L);

                //int res2 = jdbcTemplate.update(sql, paramMap);
                //log.info("user_log result:{} id:{} userId:{} age:{}",res2,userLog.getId(),userLog.getUserId(),userLog.getAge());

            }catch (Exception e){
                log.error("",e);
            }
        }
    }
}
